Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

Writing the BinCheck procedure to check inventory

In this section, you add another procedure call that illustrates some of the block types you studied in this chapter and some of the list handling functions summarized in Chapter 1, " Introducing the Progress 4GL." The procedure looks at the Warehouse and Bin tables to see which Warehouses can and cannot supply the Items for a given Order.

To modify the h-OrderCalcs.p procedure, add the new statements in bold type:

DEFINE INPUT PARAMETER piOrderNum          AS INTEGER     NO-UNDO. DEFINE OUTPUT PARAMETER pdOrderPrice        AS DECIMAL     NO-UNDO. DEFINE OUTPUT PARAMETER pdOrderTotal        AS DECIMAL     NO-UNDO. DEFINE OUTPUT PARAMETER pcWarehouseList     AS CHARACTER   NO-UNDO. DEFINE OUTPUT PARAMETER pcBestWarehouse     AS CHARACTER   NO-UNDO. DEFINE VARIABLE cItemList     AS CHARACTER     NO-UNDO. FIND order WHERE Order.orderNum = piOrderNum NO-ERROR. FOR EACH OrderLine OF Order:      ASSIGN pdOrderTotal = pdOrderTotal + OrderLine.ExtendedPrice           pdOrderPrice = pdOrderPrice + OrderLine.Price * OrderLine.Qty           cItemList = cItemList +                (IF cItemList = "" THEN "" ELSE ",") +                     STRING(ItemNum). END. RUN h-BinCheck.p (INPUT cItemList, OUTPUT pcWarehouseList, OUTPUT pcBestWarehouse).

The additional code defines two new OUTPUT parameters and a new variable.

Then, as part of the ASSIGN statement, it constructs a list of Item numbers for the OrderLines of the Order. To make a list, it uses a CHARACTER variable cItemList. The assignment statement effectively means:

Take the current value of the cItemList variable (which is initially blank).
If it’s blank, then append a blank value to it (this is just a no-op condition for the IF-THEN-ELSE statement, which requires both a THEN phrase and an ELSE phrase). Otherwise, if there’s already something in the list, append a comma to it to separate the Items.
Use the STRING built-in function to convert the integer ItemNum to a CHARACTER value and append it to the variable. (There are other built-in functions like this one named DECIMAL, INTEGER, DATE, and LOGICAL to convert character strings to those other data types as well.)

At the end of the FOR EACH block, cItemNum holds a comma-separated list of all the Items for the current Order.

Finally, the procedure runs another procedure, h-BinCheck.p, which you’ll write next.

To write the h-BinCheck.p procedure:

Save this modified version of h-OrderCalcs.p.
Open a New Procedure Window and start to write h-BinCheck.p with the following code. Add each new group of statements to the procedure as they are discussed:

/* h-BinCheck.p */ DEFINE INPUT PARAMETER pcItemList          AS CHARACTER     NO-UNDO. DEFINE OUTPUT PARAMETER pcWarehouseList     AS CHARACTER     NO-UNDO. DEFINE OUTPUT PARAMETER pcBestWarehouse     AS CHARACTER     NO-UNDO. DEFINE VARIABLE iEntry       AS INTEGER     NO-UNDO. DEFINE VARIABLE iItemNum     AS INTEGER     NO-UNDO. DEFINE VARIABLE iWHQty       AS INTEGER     NO-UNDO. DEFINE VARIABLE iWHNum       AS INTEGER     NO-UNDO. DEFINE VARIABLE iBestWH      AS INTEGER     NO-UNDO. DEFINE VARIABLE cBestList    AS CHARACTER   NO-UNDO.

This procedure takes the list of item numbers as an INPUT parameter and returns two CHARACTER parameters. The various variables are used throughout the procedure. Remember that you can use the editor shortcuts (IPC, OPC, DVI, and DVC) to generate most of the DEFINE PARAMETER and VARIABLE statements for you.

Using list and string functions to manage a list of values

The h-BinCheck.p procedure needs to make a list of how many Items are supplied by each Warehouse. There are various ways to code this, but to illustrate some more of the string manipulation functions you were introduced to in Chapter 2, "Using Basic 4GL Constructs," you’ll build this as a character string.

To update h-BinCheck.p to make a list of the number of Items supplied by each Warehouse:

Add placeholders for the count of Items in each Warehouse. The following code forms a list with as many zeroes as there are Warehouses. The zero values are later incremented to count Items supplied by each Warehouse:

FOR EACH Warehouse:           cBestList = cBestList + "0,". END. cBestList = RIGHT-TRIM(cBestList, ",").

Note: The RIGHT-TRIM function removes the final comma from the list, rather than the IF-THEN-ELSE statement in the assignment that created the item list in OrderProcs.p. These are just different ways of doing the same thing. The RIGHT-TRIM function is a bit more efficient.
To loop through the list of Items, add a DO block with the NUM-ENTRIES function:

DO iEntry = 1 TO NUM-ENTRIES(pcItemList):

NUM-ENTRIES counts the entries in a list using a comma as the delimiter between entries by default. If you need to use a delimiter other than a comma, the delimiter can be an optional second argument to the function.

Add a statement that embeds two built-in functions into one statement:

iItemNum = INTEGER(ENTRY(iEntry, pcItemList)).

The ENTRY function extracts entry number iEntry from pcItemList. It returns this to the INTEGER function, which converts the value back to an integer. So now you’ve restored the Item number to its original form.

Add a block of code that operates on this Item number. The Bin table represents bins or containers in each Warehouse that are used to store the various Items. It has both an ItemNum field to point to the Item record, and a WarehouseNum field to point to the Warehouse where the Bin is located. If the Qty (quantity) field for a Bin record is 0, then the Warehouse that Bin is in cannot supply that part. The code builds up this list of Warehouse names. The LOOKUP function looks for a string in a list. If it finds it, it returns the position of the entry in the list. Otherwise, it returns 0 if the entry is not in the list. Here the LOOKUP function is used to make sure that a Warehouse name is added to the list once only if it’s not already there:

FOR EACH Bin WHERE Bin.ItemNum = iItemNum:    IF Bin.Qty = 0 THEN    DO:        FIND Warehouse WHERE Warehouse.WarehouseNum = Bin.WarehouseNum.        IF LOOKUP(WarehouseName, pcWarehouseList) = 0 THEN            pcWarehouseList = pcWarehouseList +               (IF pcWarehouseList = "" THEN "" ELSE ",") + WarehouseName.        END.    END.

Still within the DO block that iterates on each item, add code that initializes two variables to zero using a single ASSIGN statement:

ASSIGN iWHQty = 0 iWHNum = 0.

These variables hold the quantity of each item at a Warehouse and the Warehouse number.

To use the REPEAT PRESELECT block to pre-fetch records:

Add a REPEAT block that preselects each Bin that holds the current Item, along with the Warehouse where the Bin is located, filtering these to include only Warehouses in the USA. The records are sorted in descending order of their quantity. This identifies which Warehouse has the largest quantity of the Item in inventory. Remember that the PRESELECT phrase forces Progress to retrieve all the matching records before beginning to execute the statements in the block:

REPEAT PRESELECT EACH Bin WHERE Bin.ItemNum = iItemNum,      FIRST Warehouse WHERE Warehouse.WarehouseNum = Bin.WarehouseNum AND           Warehouse.Country = "USA" BY Bin.Qty DESCENDING:

Add the code that finds the next Warehouse record in this preselected list. The first time through the REPEAT block, the FIND NEXT statement finds the first record:

FIND NEXT Warehouse.

Why does the statement name the Warehouse buffer and not the Bin? The rule is that whenever you are doing a FIND on a PRESELECT result set that involves a join, you must name the last table in the join. This makes sense, because if it is a one-to-many join, the record in the last (rightmost) table in the join is the only one to change on every iteration. The first table in the join might be the same for a number of records in the second table.

Remember also that the REPEAT block does not automatically iterate for you, even if you preselect the records. You have to use a FIND statement to move from record to record.

Add the following statements to determine whether the Warehouse with the highest inventory for the Item has a quantity at least 100 greater than the next best Warehouse. If so, it retrieves the entry in the list of best Warehouses that the code initialized with zeroes at the start of the procedure, increments it, and puts it back in the list, doing the necessary conversions to and from the INTEGER data type:

IF iWHQty NE 0 AND iWHQty - Bin.Qty > 100 THEN           DO:                ASSIGN                     iBestWH = INTEGER(ENTRY(iWHNum, cBestList))                     iBestWH = iBestWH + 1                     ENTRY(iWHNum, cBestList) = STRING(iBestWH).           END.           ELSE IF iWHQty NE 0 THEN                LEAVE.           ASSIGN iWHQty = Bin.Qty                iWHNum = Warehouse.WarehouseNum.

Terminate the REPEAT block and the DO block for each item:

END.          /* END REPEAT PRESELECT EACH Bin... */ END.          /* END DO iEntry... */

Using multiple weak-scoped references in a single block

If you take a look at the entire DO block, you can inspect the buffer scoping:

DO iEntry . . .:                         /* No buffer scoping at all */      FOR EACH Bin . . .:                 /* Weak-scoped reference to Bin */      .      .                                   /* --Bin is scoped to this block. */      .      END.      .      .      .      REPEAT PRESELECT EACH Bin. . .:     /* Weak-scoped reference to Bin */      .      .                                   /* -- Bin scoped to this block too */      .      END. END.

The DO block itself doesn’t scope any records. The FOR EACH block and the REPEAT PRESELECT EACH block each scope the Bin record with a weak scope. This is okay, and the Bin buffer is scoped to each of these two blocks in turn.

The final block of code walks through the list of best Warehouses for this Order’s items. At this point the cBestList variable holds a list of numbers for each Warehouse. Each number is the count of Items where that Warehouse has an inventory at least 100 better than the next best Warehouse. This block checks whether there’s a Warehouse that is the best for either all or all but one of the Items. If so, you find that Warehouse record and save off the WarehouseName to pass back. By now all the statements and functions in this block should be familiar to you.

To end the procedure, use the following code:

DO iEntry = 1 TO NUM-ENTRIES(cBestList):    IF INTEGER(ENTRY(iEntry, cBestList)) >= (NUM-ENTRIES(pcItemList) - 1) THEN       DO:           FIND Warehouse WHERE Warehouse.WarehouseNum = iEntry.           pcBestWarehouse = Warehouse.WarehouseName.           LEAVE.      END. END.

This procedure is a little complicated, but these examples show how the different block types interact and how to use some of the built-in functions listed in Chapter 2, " Using Basic 4GL Constructs."

Examining the scope with weak and strong references

One final question before you move on: The Bin record buffer is scoped to the two blocks inside the main DO block, but at what level is the Warehouse record buffer scoped? Look back through the entire procedure to come up with an answer before looking at this excerpt from the listing file:

File Name          Line Blk.     Type     Tran            Blk. Label -------------------- ---- --------- ---- -------------------------------- .\h-BinCheck.p          0   Procedure No      Buffers: sports2000.Warehouse .\h-BinCheck.p          14   For      No .\h-BinCheck.p          19   Do       No .\h-BinCheck.p          21   For      No      Buffers: sports2000.Bin .\h-BinCheck.p          22   Do       No .\h-BinCheck.p          32   Repeat   No      Buffers: sports2000.Bin .\h-BinCheck.p          37   Do       No .\h-BinCheck.p          51   Do       No .\h-BinCheck.p          52   Do       No

You see the two blocks where the Bin buffer is scoped. You also see that the Warehouse buffer is scoped to the entire procedure (line 0). Why is this?

There are several free references to the Warehouse buffer that aren’t in blocks that provide record scoping. This includes, among others, the final DO block of the procedure. As a result, Progress raises the scope of the buffer all the way to the procedure itself because there’s no other block to scope it to. In your sample procedure, which is only reading records from the database and not updating them, it doesn’t make a lot of difference. If the procedure had a transaction that updated the Warehouse record, though, you might find that the record and the record lock on it are held much longer than you expected or wanted, resulting in record contention between different users accessing the table at the same time.

What could you do to avoid this? Define a strong scope for the Warehouse record wherever it’s used.

To define a strong scope for the Warehouse record:

First make the first DO block around the FIND Warehouse statement scope the buffer to the block:

DO FOR Warehouse:      FIND Warehouse WHERE Warehouse.WarehouseNum = Bin.WarehouseNum.

Press SHIFT+F2 to do a syntax check. What do you get?



Why did this error happen? You tried to force the scope of the buffer to this block, but the free reference in the DO block at the end of the procedure still forces the scope up to the top, and those two conflict.

Change the final DO block to place a strong scope there:

DO FOR Warehouse:      FIND Warehouse WHERE Warehouse.WarehouseNum = iEntry.

Now a syntax check succeeds. If you compile the procedure and get a listing file, you see that the Warehouse buffer is scoped all over the place:

File Name          Line Blk.     Type     Tran            Blk. Label -------------------- ---- --------- ---- -------------------------------- .\h-BinCheck.p          0   Procedure No .\h-BinCheck.p          14   For      No      Buffers: sports2000.Warehouse .\h-BinCheck.p          19   Do       No .\h-BinCheck.p          21   For      No      Buffers: sports2000.Bin .\h-BinCheck.p          22   Do       No      Buffers: sports2000.Warehouse .\h-BinCheck.p          32   Repeat   No      Buffers: sports2000.Warehouse      Buffers: sports2000.Bin .\h-BinCheck.p          37   Do       No .\h-BinCheck.p          51   Do       No .\h-BinCheck.p          52   Do       No      Buffers: sports2000.Warehouse

Take a look at the scope for each of these blocks:

The Warehouse buffer is now scoped to the very first FOR EACH Warehouse block, with its weak scope. Progress tried to do this before. Because the other references to Warehouse forced the scope up to the procedure level, this weak scope disappeared (that’s why it’s called weak).
It’s scoped to the DO FOR Warehouse block because you added that strong-scoped reference.
It’s also scoped to the REPEAT PRESELECT block. This is another weak scope that didn’t hold when the buffer scope was forced to the top.
It’s scoped to the final DO FOR Warehouse block, with its strong-scoped reference you just added.

When you start writing serious procedures that update the database, you’ll be a lot more successful if you keep your buffer scope small like this. You should get into the habit now.